#!/use/bin/python
# coding=utf-8
# 主动服务 服务订阅数量  MySQLdb版
import datetime

import MySQLdb


def getConnection():
    conn = MySQLdb.connect(
        host='172.20.135.96',
        port=3306,
        user='pushdb',
        passwd='SkYWOrTh$TcOs',
        db='proactive_service_data',
        charset='utf8',
    )
    #conn = MySQLdb.connect(
    #    host='172.20.154.103',
    #    port=3307,
    #    user='test_dmp',
    #    passwd='DghHC3lFM1KzT3ZJ',
    #    db='proactive_service_data',
    #    charset='utf8',
    #)
    cur = conn.cursor()
    return conn, cur

# 记录 主动服务-早间开机订阅用户数 analysis_subscribe_number
def insertZjkjSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        #判断 analysis_subscribe_number 表是否已经存在当天 早间开机的数据
        selectCountSql = """
                    select count(1) num from analysis_subscribe_number t where t.service_key ='zjkj' and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日早间服务订阅数量
        selectNumSql = """
                    select count(1) subNum from life_assistant_device t where t.zjkj = 1 and t.zjkj_ui =1
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        #计算与昨天的订阅量差值 作为新增的数量
        selectBeforeCountSql = """
                    select cumulate_num from analysis_subscribe_number t where t.service_key ='zjkj' and t.ref_date =  '%s'
                """ % beforeDate
        cur.execute(selectBeforeCountSql)
        resultBeforeNum = cur.fetchone()
        newNum = 0
        if resultBeforeNum != None:
            newNum = resultNum[0] - resultBeforeNum[0]
        if countNum == 0:
            print ("%s日新增订阅早间服务设备量为%s" % (date,resultNum[0]))
            # 插入早间服务订阅设备量
            insertSql = """insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','zjkj')""" % (newNum,resultNum[0],date)
            cur.execute(insertSql)
        else:
            print ("%s日更新订阅早间服务设备量为%s" % (date,resultNum[0]))
            # 更新早间服务订阅设备量
            updateSql = """
                update analysis_subscribe_number set new_num = %s ,cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='zjkj'
            """ % (newNum,resultNum[0], date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-夜间音量订阅用户数 analysis_subscribe_number
def insertYjylSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        #判断 analysis_subscribe_number 表是否已经存在当天 夜间音量的数据
        selectCountSql = """
                    select count(1) num from analysis_subscribe_number t where t.service_key ='yjyl' and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日夜间音量订阅数量
        selectNumSql = """
                    select count(1) subNum from life_assistant_device t where t.yjyl = 1 and t.yjyl_ui =1 and t.yjyl_execute_flag = 1
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        # 计算与昨天的订阅量差值 作为新增的数量
        selectBeforeCountSql = """
                            select cumulate_num from analysis_subscribe_number t where t.service_key ='yjyl' and t.ref_date =  '%s'
                        """ % beforeDate
        cur.execute(selectBeforeCountSql)
        resultBeforeNum = cur.fetchone()
        newNum = 0
        if resultBeforeNum != None:
            newNum = resultNum[0] - resultBeforeNum[0]
        if countNum == 0:
            print ("%s日新增订阅夜间音量设备量为%s" % (date,resultNum[0]))
            # 插入夜间音量订阅设备量
            insertSql = """insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','yjyl')""" % (newNum,resultNum[0],date)
            cur.execute(insertSql)
        else:
            print ("%s日更新订阅夜间音量设备量为%s" % (date,resultNum[0]))
            # 更新夜间音量订阅设备量
            updateSql = """
                update analysis_subscribe_number set new_num = %s , cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='yjyl'
            """ % (newNum,resultNum[0], date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-健康作息订阅用户数 analysis_subscribe_number
def insertJkzxSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        #判断 analysis_subscribe_number 表是否已经存在当天 健康作息的数据
        selectCountSql = """
                    select count(1) num from analysis_subscribe_number t where t.service_key ='jkzx' and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日健康作息订阅数量
        selectNumSql = """
                    select count(1) subNum from life_assistant_device t where t.jkzx = 1 and t.jkzx_ui =1 and t.jkzx_execute_flag = 1
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        # 计算与昨天的订阅量差值 作为新增的数量
        selectBeforeCountSql = """
                                  select cumulate_num from analysis_subscribe_number t where t.service_key ='jkzx' and t.ref_date =  '%s'
                              """ % beforeDate
        cur.execute(selectBeforeCountSql)
        resultBeforeNum = cur.fetchone()
        newNum = 0
        if resultBeforeNum != None:
            newNum = resultNum[0] - resultBeforeNum[0]
        if countNum == 0:
            print ("%s日新增订阅健康作息设备量为%s" % (date,resultNum[0]))
            # 插入健康作息订阅设备量
            insertSql = """insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','jkzx')""" % (newNum,resultNum[0],date)
            cur.execute(insertSql)
        else:
            print ("%s日更新订阅健康作息设备量为%s" % (date,resultNum[0]))
            # 更新健康作息订阅设备量
            updateSql = """
                update analysis_subscribe_number set new_num = %s, cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='jkzx'
            """ % (newNum,resultNum[0], date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-3个服务任意服务(总体)订阅用户数 analysis_subscribe_number
def insertAllSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        #判断 analysis_subscribe_number 表是否已经存在当天 总体订阅设备量的数据
        selectCountSql = """
                    select count(1) num from analysis_subscribe_number t where t.service_key ='all' and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日总体订阅设备量
        selectNumSql = """
                    select count(1) subNum from life_assistant_device t where (t.zjkj = 1 and t.zjkj_ui =1) 
                        or (t.jkzx = 1 and t.jkzx_ui =1 and t.jkzx_execute_flag = 1) or (t.yjyl = 1 and t.yjyl_ui =1 and t.yjyl_execute_flag = 1)
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        # 计算与昨天的订阅量差值 作为新增的数量
        selectBeforeCountSql = """
                                  select cumulate_num from analysis_subscribe_number t where t.service_key ='all' and t.ref_date =  '%s'
                              """ % beforeDate
        cur.execute(selectBeforeCountSql)
        resultBeforeNum = cur.fetchone()
        newNum = 0
        if resultBeforeNum != None:
            newNum = resultNum[0] - resultBeforeNum[0]
        if countNum == 0:
            print ("%s日新增总体订阅设备量为%s" % (date,resultNum[0]))
            # 插入总体订阅设备量
            insertSql = """insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key) 
                value(%s,%s,'%s','all')""" % (newNum,resultNum[0],date)
            cur.execute(insertSql)
        else:
            print ("%s日更新总体订阅设备量为%s" % (date,resultNum[0]))
            # 更新总体订阅设备量
            updateSql = """
                update analysis_subscribe_number set new_num = %s, cumulate_num = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='all'
            """ % (newNum,resultNum[0], date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date


if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    beforeDate = getdate(2)
    #today = "2021-12-13"
    print ("%s 日期,更新报表" % date)
    insertZjkjSubscribeNumber(date,beforeDate)
    insertYjylSubscribeNumber(date,beforeDate)
    insertJkzxSubscribeNumber(date,beforeDate)
    insertAllSubscribeNumber(date,beforeDate)

